package gem.model.dao.mysql;

import gem.model.core.Course;
import gem.model.core.ScienceDomain;
import gem.database.Database;
import gem.model.dao.Common;
import gem.model.dao.DAOFactory;
import gem.model.dao.ScienceDomainDAO;

import java.sql.SQLException;
import java.util.Collection;

public class MySqlScienceDomainDAO implements ScienceDomainDAO
{

    private static ScienceDomain defaultScience = null;
    
    public static final ScienceDomain testGetDefaultScienceDomain() throws SQLException
    {
        return getDefaultScienceDomain();
    }

    static final ScienceDomain getDefaultScienceDomain() throws SQLException
    {
        if( defaultScience == null )
        {
            String query = "SELECT * FROM ScienceDomain WHERE sciencedomaintitle = " + '"' + '"' + ";";
            java.sql.Connection conn = null;
            java.sql.ResultSet rs = null;
            try
            {
                conn = Database.getConnection();
                rs = conn.createStatement().executeQuery(query);

                if( rs.first() )
                {
                    defaultScience = new ScienceDomain().setId( rs.getInt(Common.ScienceDomainId) ).setTitle(rs.getString( Common.ScienceDomainTitle));
                }
                else // there is no entry, we are creating new one!
                {
                    query = "INSERT INTO sciencedomain( sciencedomaintitle ) VALUES( '" + "" + "'); ";
                    int newId;

                    java.sql.PreparedStatement pStat = conn.prepareStatement(query, java.sql.Statement.RETURN_GENERATED_KEYS);
                    int numRows = pStat.executeUpdate();

                    if (numRows == 0)
                    {
                        throw new SQLException("Cannot insert default sciencedomain " );
                    }

                    rs = pStat.getGeneratedKeys();

                    if (rs.first())
                    {
                        newId = rs.getInt(1);
                        defaultScience = new ScienceDomain().setId(newId).setTitle("");
                    }
                    else
                    {
                        throw new SQLException("Cannot insert default course " );
                    }
                }
            }
            finally
            {
                if( rs != null )
                {
                    rs.close();
                }
                if( conn != null )
                {
                    conn.close();
                }
            }
        }

        return defaultScience;
    }


    @Override
    public int insertScienceDomain(ScienceDomain s) throws SQLException
    {
        // TODO Auto-generated method stub
        // 1. Insert into Department table
        // 2. Insert into ScienceDomainCourse, with value 0 for Course ( non-exist yet any course )
        String query = "INSERT INTO sciencedomain( sciencedomaintitle ) VALUES ( '" + s.getTitle() + "' );";
        java.sql.Connection conn = null;
        java.sql.ResultSet rs = null;
        int newId = -1;

        try
        {
            conn = Database.getConnection();
            java.sql.PreparedStatement pStat = conn.prepareStatement(query, java.sql.Statement.RETURN_GENERATED_KEYS);

            int numRows = pStat.executeUpdate();

            int depId = s.getDepartment().getId();
            int cId = MySqlCourseDAO.getDefaultCourse().getId();

            if (numRows == 0)
            {
                throw new SQLException("Cannot insert course " + s);
            }

            rs = pStat.getGeneratedKeys();

            if (rs.first())
            {
                newId = rs.getInt(1);
                s.setId(newId);
            }
            else
            {
                throw new SQLException("Cannot insert sciencedomain " + s);
            }

            query = "INSERT INTO sciencedomaincourses( departmentid, sciencedomainid, courseid ) VALUES "
                    + "( " + depId + " , " + s.getId() + " , " + cId + " ); ";

            conn.createStatement().executeUpdate(query);

        }
        catch (SQLException e)
        {
            throw e;
        } finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }

        return newId;
    }

    @Override
    public boolean deleteScienceDomain(ScienceDomain s) throws SQLException
    {

        // TODO Auto-generated method stub
        java.sql.Connection conn = null;
        String query = "DELETE FROM sciencedomain WHERE sciencedomainId = '" + s.getId() + "'; ";

        boolean foundCourse = false;

        try
        {
            conn = Database.getConnection();
            // removing from sciencedomain will remove from sciencedomaincourse as well
            conn.createStatement().executeUpdate(query);

            // fethcin id's of all courses belongs to s
            String in = "(";
            java.util.Collection<Course> cs = s.getCourses();
            if (cs != null && cs.size() > 0)
            {
                for (Course c : s.getCourses())
                {
                    in += c.getId() + ", ";
                }
                in = in.substring(0, in.lastIndexOf(","));
                in += ")";

                // left: has to remove courses from course table
                query = "DELETE FROM course where courseid IN " + in + " ; ";
                conn.createStatement().executeUpdate(query);
            }
        }
        catch (SQLException e)
        {
            throw e;
        }
        finally
        {
            if (conn != null)
            {
                conn.close();
            }
        }

        return true;
    }

    @Override
    public boolean updateScienceDomain(ScienceDomain s) throws SQLException
    {
        // TODO Auto-generated method stub
        String query = "UPDATE sciencedomain SET sciencedomaintitle = '" + s.getTitle() + "' WHERE "
                + " sciencedomainid = '" + s.getId() + "' ;";

        java.sql.Connection conn = null;

        try
        {
            conn = Database.getConnection();
            conn.createStatement().executeUpdate(query);
        }
        catch (SQLException e)
        {
            throw e;
        } finally
        {
            if (conn != null)
            {
                conn.close();
            }
        }

        return true;
    }

    @Override
    public ScienceDomain findScienceDomain(String s) throws SQLException
    {
        String query = "SELECT * FROM sciencedomain WHERE sciencedomaintitle ='" + s + "';";
        java.sql.Connection conn = null;
        java.sql.ResultSet rs = null;
        ScienceDomain sd = null;

        try
        {
            conn = Database.getConnection();
            rs = conn.createStatement().executeQuery(query);
            if (rs.first())
            {
                sd = new ScienceDomain().setId(rs.getInt(Common.ScienceDomainId)).setTitle(rs.getString(Common.ScienceDomainTitle));
            }
        } finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }

        return sd;
    }

    @Override
    public Collection<Course> listCourses(ScienceDomain s) throws SQLException
    {
        // TODO Auto-generated method stub
        String query = "SELECT DISTINCT c.courseid, coursetitle FROM course c, sciencedomaincourses sd WHERE "
                + "c.courseid = sd.courseid AND sd.sciencedomainid = '" + s.getId() + "';";
        java.sql.Connection conn = null;
        java.sql.ResultSet rs = null;
        java.util.Collection<Course> courses = new java.util.Vector();

        try
        {
            conn = Database.getConnection();
            rs = conn.createStatement().executeQuery(query);

            while (rs.next())
            {
                courses.add(new Course().setId(rs.getInt(Common.CourseId)).setTitle(rs.getString(Common.CourseTitle)));
            }
        } finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }

        return courses;
    }

    @Override
    public boolean moveCourse(Course c, ScienceDomain from, ScienceDomain to) throws SQLException
    {
        String query = "UPDATE sciencedomaincourses SET sciencedomainid = '" + to.getId() + "' WHERE "
                + " courseid = '" + c.getId() + "'";

        java.sql.Connection conn = null;
        
        try
        {
            conn = Database.getConnection();
            conn.createStatement().executeUpdate(query);
        }
        finally
        {
            if (conn != null)
            {
                conn.close();
            }
        }

        return true;
    }

}
